package net.messagenet.mazor.infra.excel;


import java.io.File;
import java.io.IOException;
import java.util.Locale;
import java.util.Properties;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import net.messagenet.mazor.DO.PosDO;
import net.messagenet.mazor.DO.PosProductDO;

public class PosRequestExcelHandler {
private Properties excelConstats;
	
	
	private WritableCellFormat headCell;
	private WritableCellFormat dataCell;
	private WritableCellFormat dateCell;
	
	private WritableSheet excelSheet;


	private WritableCellFormat colTitleCell;

	/**
	 * c'tor
	 * 
	 * @param excelConstats properties containing the constants for the excel sheet
	 */
	public PosRequestExcelHandler(Properties excelConstats) {
		this.excelConstats = excelConstats;
	}

	/**
	 * This method writes a dpa request object to excel according to the formmat
	 * 
	 * @param dpa The dpa object which data will be written to the excel
	 * @param targetFile the full path of the file which the data wll be written to
	 * @throws IOException
	 * @throws WriteException
	 */
	public void writePOSExcelFile(PosDO pos,String targetFile) throws IOException, WriteException{
		File excelFile = new File(targetFile);
		WorkbookSettings wbSettings = new WorkbookSettings();

		wbSettings.setLocale(new Locale("en", "EN"));

		WritableWorkbook workbook = Workbook.createWorkbook(excelFile, wbSettings);
		workbook.createSheet(excelConstats.getProperty("pos_sheet_name"), 0);
		excelSheet = workbook.getSheet(0);
		createWritersAndView();
		createHead(pos);
		createLabel();
		createContent(pos);
		

		workbook.write();
		workbook.close();
		
	}



	/**
	 * This method creates the writes for the cells and sets the colum width
	 * 
	 * @throws WriteException
	 */
	private void createWritersAndView() throws WriteException {
		WritableFont lablesWriterFont = new WritableFont(WritableFont.ARIAL,9);
		headCell = new WritableCellFormat(lablesWriterFont);
		headCell.setBackground(Colour.YELLOW);
		headCell.setAlignment(Alignment.CENTRE);
		headCell.setWrap(true);
		headCell.setBorder(Border.ALL, BorderLineStyle.THIN);
		
		WritableFont dataWriterFont = new WritableFont(WritableFont.ARIAL,9);
		colTitleCell = new WritableCellFormat(dataWriterFont);
		colTitleCell.setAlignment(Alignment.CENTRE);
		colTitleCell.setWrap(true);
		
		dataCell = new WritableCellFormat(dataWriterFont);
		dataCell.setAlignment(Alignment.CENTRE);
		dataCell.setWrap(true);
		
		dateCell =new WritableCellFormat ( new DateFormat ("dd/mm/yyyy"));
		dateCell.setAlignment(Alignment.CENTRE);
		dateCell.setWrap(true);

		
		CellView cv = new CellView();
		
		cv.setAutosize(true);
		excelSheet.setColumnView(0, 30);
		excelSheet.setColumnView(1, 30);
		excelSheet.setColumnView(2, 30);
		excelSheet.setColumnView(3, 30);
		excelSheet.setColumnView(4, 30);
		excelSheet.setColumnView(5, 30);
		excelSheet.setColumnView(6, 40);
		excelSheet.setColumnView(7, 50);
		excelSheet.setColumnView(8, 80);
		excelSheet.setColumnView(9, 70);
		excelSheet.setColumnView(10, 60);
		excelSheet.setColumnView(11, 20);
		excelSheet.setColumnView(12, 20);
		excelSheet.setColumnView(13, 10);
		excelSheet.setColumnView(14, 50);
		excelSheet.setColumnView(15, 80);
	}
	
	/**
	 * This method creates the head of the pos the email address and  the dis name
	 * @param pos 
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 * 
	 */
	private void createHead(PosDO pos) throws RowsExceededException, WriteException {
		writeText(0, 0, excelConstats.getProperty("pos_dis_name"));
		writeHead(0, 1, excelConstats.getProperty("pos_dis_name_messagenet"));
		writeText(1, 0, excelConstats.getProperty("pos_email"));
		writeHead(1, 1, pos.getEmail());
	}
	


	/**
	 * creates all the cells containing data according to the pos object
	 * 
	 * @param pos
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void createContent(PosDO pos) throws RowsExceededException, WriteException {

		
		int i=5;
		for (PosProductDO prd : pos.getPosProducts()) {
			writeNumber(i, 0, prd.getCustomer().getAccountancyId());
			writeText(i, 1,  prd.getCustomer().getName());
			writeText(i, 2,  prd.getCustomer().getLocation());
			writeText(i, 3,  prd.getCustomer().getState());
			writeNumber(i,4,  prd.getCustomer().getZipCode());
			writeText(i, 5,  prd.getCustomer().getCountry());
			
			writeNumber(i, 6,  prd.getInvoiceNumber());
			writeNumber(i, 7,  prd.getInvoiceLine());
			if (prd.getDpaId()!=null && !prd.getDpaId().equals("")){
				writeText(i,8,  prd.getDpaId());
				writePrice(i,10,  prd.getDpaCost());
			}
			writePrice(i, 9,  prd.getSdc());
			writeText(i, 11,  prd.getProduct().getDialogicItemKey());
			writeText(i, 12,  prd.getProduct().getProductName());
			writeNumber(i,13,  prd.getQuantity());
			writeText(i, 14,  prd.getCurrency());
			writePrice(i, 15,  prd.getResalePrice());
			DateTime reqDate=new DateTime(16, i, pos.getDate(), dateCell);
			excelSheet.addCell(reqDate);
			
			i++;
		}
	}
	/**
	 * creates all the labels for the excel sheet
	 * 
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void createLabel() throws RowsExceededException, WriteException {
		writeColTile(3, 0,  excelConstats.getProperty("pos_usr_no_t"));
		writeColTile(3, 1,  excelConstats.getProperty("pos_usr_name_t"));
		writeColTile(3, 2,  excelConstats.getProperty("pos_usr_city_t"));
		writeColTile(3, 3,  excelConstats.getProperty("pos_usr_state_t"));
		writeColTile(3, 4,  excelConstats.getProperty("pos_usr_zip_t"));
		writeColTile(3, 5,  excelConstats.getProperty("pos_usr_cnty_t"));
		writeColTile(3, 6,  excelConstats.getProperty("pos_inv_num_t"));
		writeColTile(3, 7,  excelConstats.getProperty("pos_inv_line_t"));
		writeColTile(3, 8,  excelConstats.getProperty("pos_dpa_nun_t"));
		writeColTile(3, 9,  excelConstats.getProperty("pos_sdc_t"));
		writeColTile(3, 10,  excelConstats.getProperty("pos_dpa_cost_t"));
		writeColTile(3, 11,  excelConstats.getProperty("pos_itm_num_t"));
		writeColTile(3, 12,  excelConstats.getProperty("pos_itm_desc_t"));
		writeColTile(3, 13,  excelConstats.getProperty("pos_qty_t"));
		writeColTile(3, 14,  excelConstats.getProperty("pos_curr_t"));
		writeColTile(3, 15,  excelConstats.getProperty("pos_dist_price_t"));
		writeColTile(3, 16,  excelConstats.getProperty("pos_ship_date_t"));
		
		
		writeText(4, 0,  excelConstats.getProperty("pos_usr_no_s"));
		writeText(4, 1,  excelConstats.getProperty("pos_usr_name_s"));
		writeText(4, 2,  excelConstats.getProperty("pos_usr_city_s"));
		writeText(4, 3,  excelConstats.getProperty("pos_usr_state_s"));
		writeText(4, 4,  excelConstats.getProperty("pos_usr_zip_s"));
		writeText(4, 5,  excelConstats.getProperty("pos_usr_cnty_s"));
		writeText(4, 6,  excelConstats.getProperty("pos_inv_num_s"));
		writeText(4, 7,  excelConstats.getProperty("pos_inv_line_s"));
		writeText(4, 8,  excelConstats.getProperty("pos_dpa_nun_s"));
		writeText(4, 9,  excelConstats.getProperty("pos_sdc_s"));
		writeText(4, 10,  excelConstats.getProperty("pos_dpa_cost_s"));
		writeText(4, 11,  excelConstats.getProperty("pos_itm_num_s"));
		writeText(4, 12,  excelConstats.getProperty("pos_itm_desc_s"));
		writeText(4, 13,  excelConstats.getProperty("pos_qty_s"));
		writeText(4, 14,  excelConstats.getProperty("pos_curr_s"));
		writeText(4, 15,  excelConstats.getProperty("pos_dist_price_s"));
		writeText(4, 16,  excelConstats.getProperty("pos_ship_date_s"));
		
	}

	/**
	 * This method writes the columns titles of the pos (b02 ect)
	 * 
	 * @param row
	 * @param column
	 * @param s
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void writeColTile(int row, int column, String s) throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, s, colTitleCell);
		excelSheet.addCell(label);
	}
	/**
	 * This method writes a text as head of the pos report
	 * 
	 * @param row
	 * @param column
	 * @param s
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void writeHead(int i, int j, String property) throws RowsExceededException, WriteException {
		Label label;
		label = new Label(j, i, property, headCell);
		excelSheet.addCell(label);
		
	}
	
	/**
	 * This method writes a number into a cell 
	 * 
	 * @param row
	 * @param column
	 * @param integer
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	private void writeNumber( int row, int column,Integer integer) throws WriteException, RowsExceededException {
		Number number;
		number = new Number(column, row, integer, dataCell);
		excelSheet.addCell(number);
	}
	/**
	 * This method writes a text  into a cell
	 * 
	 * @param row
	 * @param column
	 * @param s
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void writeText( int row, int column,String str) throws WriteException, RowsExceededException {
		Label label;
		label = new Label(column, row, str, dataCell);
		
		excelSheet.addCell(label);
	}

	/**
	 * This method prices  into a cell 
	 * 
	 * @param row
	 * @param column
	 * @param integer
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	private void writePrice(int row, int col, double sdc) throws RowsExceededException, WriteException {
		Number number;
		number = new Number(col, row, sdc, dataCell);
		excelSheet.addCell(number);
		
	}



}
